package com.gjintel.business.dbmanage.controller;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;

import net.sf.json.JSONArray;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.gjintel.business.dbmanage.service.IDataBaseManageService;
import com.gjintel.framework.constant.Constant;

/**
 * 数据库管理controller
 * 
 * @author xuyefei 2016-03-16
 * 
 */
@Controller
@RequestMapping("/business/dbmanage")
public class DataBaseManageController {
	
	@Resource
	private IDataBaseManageService dataBaseManaerServiceImpl;
	
	private static String DATA_BASE_NAME;// 数据库名称
	private static String DATA_BASE_USER_NAME;// 数据库用户名
	private static String DATA_BASE_PASSWORD;// 密码
	private static String DATA_BASE_HOST;// 数据库主机IP
	private static String DATA_BASE_PORT;// 数据库监听端口
	private static String MYSQL_BIN_PATH;// mysql的bin目录
	static {
		Properties prop = new Properties();
		InputStream in = DataBaseManageController.class.getResourceAsStream("/dataBackUp.properties");
		try { 
			prop.load(in);
			DATA_BASE_NAME = prop.getProperty("data_base_name");
			DATA_BASE_USER_NAME = prop.getProperty("data_base_user_name");
			DATA_BASE_PASSWORD = prop.getProperty("data_base_password");
			DATA_BASE_HOST = prop.getProperty("data_base_host");
			DATA_BASE_PORT = prop.getProperty("data_base_port");
			MYSQL_BIN_PATH = prop.getProperty("mysql_bin_path");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 数据库备份
	 * 
	 * @param session
	 * @param backUpType
	 *            备份类型：全库备份、选定表备份
	 * @param backUpDataBaseAlias
	 *            数据库备份别名
	 * @param tableNames
	 *            选定的要备份的表名称
	 * @param backUpTableAlias
	 *            备份表的别名 author xuyefei 2016-03-16
	 * @return
	 */
	@RequestMapping("/backup")
	@ResponseBody
	public String dataBaseBackUp(HttpSession session, int backupType,
			String backUpFileAlias, String[] tableNames) {
		String backUpPath = session.getServletContext().getRealPath(
				"/data_backup");
		// 操作系统名称
		String os = System.getProperty("os.name").toUpperCase();
		// mysql执行命令
		StringBuilder command = new StringBuilder();
		if (os.indexOf("WINDOWS") != -1) {// windows操作系统
			command.append("cmd /c ").append(MYSQL_BIN_PATH)
					.append("mysqldump -h").append(DATA_BASE_HOST)
					.append(" -P").append(DATA_BASE_PORT)// 大写的P是端口，小写的p是密码
					.append(" -u").append(DATA_BASE_USER_NAME).append(" -p")
					.append(DATA_BASE_PASSWORD).append(" ").append(DATA_BASE_NAME);
		} else if (os.indexOf("LINUX") != -1) {// linux操作系统，暂时未测试
			// TODO  linux操作系统 mysql备份
		}
		// 生成备份的文件路径
		File backUpDirectory = new File(backUpPath);
		if(!backUpDirectory.exists()){
			backUpDirectory.mkdirs();
		}
		// 判断自定义的别名是否已重复
		File[] backUpFiles = backUpDirectory.listFiles();
		for(File file : backUpFiles){
			if(file.getName().equals(backUpFileAlias)){
				return "FILE EXISTS";
			}
		}
		if (backupType == 1) {// 全库备份
			command.append(" > ").append(backUpDirectory).append("\\").append(backUpFileAlias).append(".sql");
		} else {// 选定表备份
			for(String tableName : tableNames){
				command.append(" ").append(tableName);
			}
			command.append(" > ").append(backUpPath).append("\\").append(backUpFileAlias).append(".sql");
		}
		try {
			Process process = Runtime.getRuntime().exec(command.toString());
			if(process.waitFor() == 0){// 进程正常结束
				return Constant.SAVE_SUCCESS;
			}else{
				return Constant.SAVE_UN_SUCCESS;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return Constant.SAVE_UN_SUCCESS;
		}
	}
	
	/**
	 * 数据库恢复
	 * @param session
	 * @param recoverFileName 选择恢复的文件名
	 * @return
	 * @throws IOException 
	 */
	@RequestMapping("/restore")
	@ResponseBody
	public String restore(HttpSession session, String sqlPath){
	    String command = "cmd /c " + MYSQL_BIN_PATH + "mysql -h" + DATA_BASE_HOST + " -P"  
	                + DATA_BASE_PORT + " -u" + DATA_BASE_USER_NAME + " -p"  
	                + DATA_BASE_PASSWORD + " --default-character-set=utf8 "  
	                + DATA_BASE_NAME + " < " + sqlPath;
	    try {
		Process process = Runtime.getRuntime().exec(command.toString());
		if(process.waitFor() == 0){// 进程正常结束
			return Constant.SAVE_SUCCESS;
		}else{
			return Constant.SAVE_UN_SUCCESS;
		}
	    }catch(Exception e){
	    	e.printStackTrace();
	    	return Constant.SAVE_UN_SUCCESS;
	    }
	}
	
	/**
	 * 数据库恢复
	 * @param session
	 * @param recoverFileName 选择恢复的文件名
	 * @return
	 * @throws IOException 
	@RequestMapping("/restore")
	@ResponseBody
	public String restore(HttpSession session, String sqlPath){
		try{
			Runtime runtime = Runtime.getRuntime();
	        //因为在命令窗口进行mysql数据库的导入一般分三步走，所以所执行的命令将以字符串数组的形式出现
	        String cmdarray[] = getImportCommand(sqlPath);//根据属性文件的配置获取数据库导入所需的命令，组成一个数组  
	        Process process = runtime.exec(cmdarray[0]);
	        //执行了第一条命令以后已经登录到mysql了，所以之后就是利用mysql的命令窗口
	        //进程执行后面的代码
	        OutputStream os = process.getOutputStream();
	        OutputStreamWriter writer = new OutputStreamWriter(os);
	        //命令1和命令2要放在一起执行
	        writer.write(cmdarray[1] + "\r\n" + cmdarray[2]);
	        writer.flush();
	        writer.close();
	        os.close();
	        return Constant.SAVE_SUCCESS;
		}catch(Exception e){
			return Constant.SAVE_UN_SUCCESS;
		}
	}*/
	
	/** 
     * 根据属性文件的配置，分三步走获取从目标文件导入数据到目标数据库所需的命令 
     * 如果在登录的时候指定了数据库名则会 
     * 直接转向该数据库，这样就可以跳过第二步，直接第三步；  
     * @param properties 
     * @return 
    private static String[] getImportCommand(String sqlPath) {  
        //获取登录命令语句  
        String loginCommand = new StringBuffer().append("mysql -u").append(DATA_BASE_USER_NAME).append(" -p").append(DATA_BASE_PASSWORD).append(" -h").append(DATA_BASE_HOST)  
        .append(" -P").append(DATA_BASE_PORT).toString();  
        //获取切换数据库到目标数据库的命令语句  
        String switchCommand = new StringBuffer("use ").append(DATA_BASE_NAME).toString();  
        //获取导入的命令语句  
        String importCommand = new StringBuffer("source ").append(sqlPath).toString();  
        //需要返回的命令语句数组  
        String[] commands = new String[] {loginCommand, switchCommand, importCommand};  
        return commands;  
    }*/
	
	/**
	 * 查询当前库中所有的表
	 * @return
	 */
	@RequestMapping("/allTableNames")
	@ResponseBody
	public JSONArray dataBaseAllTables(){
		List<Map<String,String>> resultList = this.dataBaseManaerServiceImpl.getAllTableNames(DATA_BASE_NAME);
		return JSONArray.fromObject(resultList);
	}
	
	public static void main(String[] args) throws IOException, InterruptedException {
		/**String command = "cmd /c C:\\wamp\\bin\\mysql\\mysql5.6.17\\bin\\mysqldump -h127.0.0.1 -P3306 -uroot -ptest --default-character-set=gb2312 e_shop_db > E:/aaa.sql";
		Process process = Runtime.getRuntime().exec(command.toString());
		if(process.waitFor() == 0){// 进程正常结束
			System.out.println("yes");
		}else{
			System.out.println("no");
		}*/
		/**
		String database_tools_path = MYSQL_BIN_PATH;
	    String command = "cmd /c " + database_tools_path + "mysql -h" + DATA_BASE_HOST + " -P"  
	                + DATA_BASE_PORT + " -u" + DATA_BASE_USER_NAME + " -p"  
	                + DATA_BASE_PASSWORD + " --default-character-set=utf8 "  
	                + DATA_BASE_NAME + " < e:/aaa.sql";  
	  
		Process process = Runtime.getRuntime().exec(command.toString());
		if(process.waitFor() == 0){// 进程正常结束
			System.out.println("yes");
		}else{
			System.out.println("no");
		}*/
	}
}
